if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CloneAnswers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CloneAnswers]
GO

/*
* Copy answers for with the specified old parent answer id into temp table replacing the old parent answer id with new parent answer id. 
* Initially both old and new parent answer ids would be passed in as zeroes. These constitute the top-level answers.
* Each record in the temp table is inserted into the 'Answer' table. To insert this records children, the id
* before inserted will now be the old parent answer id and the id after inserting will be the new parent answer id.
* The function is now called with these values recursively.
*/

Create procedure [dbo].[CloneAnswers]
(
	@oldParentAnswerId INT,
	@newParentAnswerId INT,
	@newQAId INT,
	@oldQAId INT
)
AS

BEGIN

	DECLARE @recordCount INT
	DECLARE @answerIndex INT
	DECLARE @TempAnswer TABLE(
	[ROWNUM] [int] IDENTITY(1, 1),
	[ID] [int] NOT NULL,
	[QuestionnaireAnswerId] [int] NOT NULL,
	[Name] [varchar](256) NOT NULL,
	[Value] [varchar](4000) NULL,
	[GroupIndicator] [bit] NOT NULL DEFAULT 0,
	[ParentAnswerId] [int] NULL
	)

	SET @answerIndex = 1


	
	INSERT INTO @TempAnswer (ID, QuestionnaireAnswerId, Name, Value, GroupIndicator, ParentAnswerId)
	SELECT Id, @newQAId, Name, Value, GroupIndicator, @newParentAnswerId
	FROM Answer
	WHERE ISNULL(ParentAnswerId, 0) = @oldParentAnswerId
	AND QuestionnaireAnswerId = @oldQAId

	SELECT @recordCount = count(*) from @TempAnswer

	WHILE (@answerIndex <= @recordCount)
	BEGIN
		SELECT @oldParentAnswerId = [Id] FROM @TempAnswer WHERE RowNum = @answerIndex

		INSERT INTO Answer (QuestionnaireAnswerId, Name, Value, GroupIndicator, ParentAnswerId )
		SELECT QuestionnaireAnswerId, Name, Value, GroupIndicator, case  when ParentAnswerId = 0 then null else parentanswerid end
		FROM @TempAnswer
		WHERE RowNum = @answerIndex

		SET @answerIndex = @answerIndex + 1
		SELECT @newParentAnswerId = IDENT_CURRENT('Answer')

		Exec CloneAnswers @oldParentAnswerId, @newParentAnswerId, @newQAId, @oldQAId

	END	
END